package dbLayer;

import modelLayer.Subject;
import modelLayer.Teacher;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBSubject implements DBSubjectIF {
	
	private Connection dbConnection;
	
	public DBSubject() {
		
		this.dbConnection = DBConnection.getInstance().getDBcon();
		
	}
	@Override
	public int insert(String name, String description) throws SQLException {
		
		int rc = -1;
		String query = "INSERT INTO Subject (name, description) "
					   + "values(?, ?)";		
		try {
			PreparedStatement ps = dbConnection.prepareStatement(query);
			ps.setString(1, name);
			ps.setString(2, description);
			//ps.setInt(3, t.getId());
			rc = ps.executeUpdate();
			ps.close();
		} 
		catch (SQLException e) {
			System.out.println("DEBUG MESSAGE [SUBJECT]: insert(): " + e);
			throw new SQLException();
		}	
		
		return rc;

	}
	
	@Override
	public Subject find(String name, boolean retrieveAssociation) throws Exception {
		
		//String wClause = "subject_id = id and name = '" + name + "'"; 
		String wClause = "name = '" + name + "'";
		return singleWhere(wClause, retrieveAssociation);
		
	}
	
	public Subject findById(int id, boolean retrieveAssociation) {
		
		//String wClause = "subject_id = id and id = '" + id + "'"; 
		String wClause = "id = '" + id + "'";
		return singleWhere(wClause, retrieveAssociation);
		
	}
	
	@Override
	public List<Subject> getAll(boolean retrieveAssociation) {
		
		//return miscWhere("subject_id = id", retrieveAssociation);
		return miscWhere("", retrieveAssociation);
		
	}

	@Override
	public int update(int id, String newName, String newDescription) throws SQLException {
		
		int rc = -1;
		String query = "UPDATE Subject SET name = ?, description = ? where id = ?"; 
					  
		try {
			PreparedStatement ps = dbConnection.prepareStatement(query);
			ps.setString(1, newName);
			ps.setString(2, newDescription);
			ps.setInt(3, id);
			rc = ps.executeUpdate();
			ps.close();
		} 		 
		catch (SQLException e) {
			System.out.println("DEBUG MESSAGE [SUBJECT]: update(): " + e);
			throw new SQLException();
		}	
		
		return rc;
		
	}
	@Override
	public int remove(String name) throws SQLException {
		
		int rc = -1;
		String query = "DELETE FROM Subject WHERE name = '" + name + "'";
		try {
			Statement stmt = dbConnection.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch (SQLException ex) {
			throw new SQLException();
		}
		catch (Exception e) {
			System.out.println("DEBUG MESSAGE [SUBJECT]: remove(): " + e);
		}
		
		return rc;
		
	}
	
	/**
	 * Builds a query with a where clause (condition), and returns a single object
	 * to the caller. This method is used when only wanting one object built from 
	 * the database. This method takes the following parameters:
	 *  
	 * @param 	wClause				String		the search condition, and where clause of the SQL query.
	 * @param 	retriveAssociation	boolean		states if we're to retrieve associations in the object aswell.
	 * @return	s					Subject		a built Subject object, based on query results from the database.
	 */
	private Subject singleWhere(String wClause, boolean retrieveAssociation) {
		
		ResultSet rs;
		Subject s = null;
		String query = buildQuery(wClause);
		System.out.println(query);

		try {
			Statement stmt = dbConnection.createStatement();
			stmt.setQueryTimeout(5);
			rs = stmt.executeQuery(query);
			if (rs.next()) {
				s = buildObject(rs);

				String query2 = "SELECT teacher_id from Teacher_Subject where subject_id = " + s.getId();
				System.out.println(query2);
				Statement stmt2 = this.dbConnection.createStatement();
				ResultSet rs2 = stmt2.executeQuery(query2);
				while(rs2.next()) {
					s.addTeacher(new Teacher(rs2.getInt("teacher_id")));
				}
				stmt2.close();
				
				if (retrieveAssociation) {
					DBTeacher dbTeacher = new DBTeacher();
					
					List<Teacher> t2List = new ArrayList<Teacher>();
					for(Teacher t : s.getTeachers()) {
						Teacher teacher = dbTeacher.findById(t.getId());
						t2List.add(teacher);
					}
					s.setTeachers(t2List);
				}
			} 
			stmt.close();
		}
		catch (Exception e) {
			System.out.println("DEBUG MESSAGE [SUBJECT]: singleWhere(): " + e);
		}
		
		return s;		

	}
	
	/**
	 * Builds a query with a where clause (condition), and returns a collection of objects
	 * to the caller. This method is used when only wanting one or more objects built from 
	 * the database. This method takes the following parameters:
	 *  
	 * @param 	wClause				String				the search condition, and where clause of the SQL query.
	 * @param 	retriveAssociation	boolean				states if we're to retrieve associations in the object aswell.
	 * @return	list				List<Subject>		a list of built Subject objects, based on query results from the 
	 * 													database.
	 */
	private List<Subject> miscWhere(String wClause, boolean retrieveAssociation) {
		
		ResultSet rs;
		String query = buildQuery(wClause);
		System.out.println("miscWhere: " + query);
		List<Subject> list = new ArrayList<Subject>();
		
		try {
			Statement stmt = this.dbConnection.createStatement();
			stmt.setQueryTimeout(5);
			rs = stmt.executeQuery(query);
			DBTeacher dbTeacher = new DBTeacher();
			
			while (rs.next()) { 
				Subject s = buildObject(rs);
				
				
				String query2 = "SELECT teacher_id from Teacher_Subject where subject_id = " + s.getId();
				Statement stmt2 = this.dbConnection.createStatement();
				ResultSet rs2 = stmt2.executeQuery(query2);
				while(rs2.next()) {
					s.addTeacher(new Teacher(rs2.getInt("teacher_id")));
				}
				stmt2.close();
				
				if (retrieveAssociation) {
					List<Teacher> t2List = new ArrayList<Teacher>();
					for(Teacher t : s.getTeachers()) {
						Teacher teacher = dbTeacher.findById(t.getId());
						t2List.add(teacher);
					}
					s.setTeachers(t2List);					
				}
				list.add(s);
			}
			stmt.close();
		}
 		catch(Exception e) {
	 		System.out.println("DEBUG MESSAGE [SUBJECT]: miscWhere(): " + e);
 		}
		return list;
		
	}

	/**
	 * Builds a query with a where clause (condition), and returns a string to the caller.
	 * This method takes the following parameters:
	 *  
	 * @param 	wClause				String		the search condition, and where clause of the SQL query.
	 * @return	query				String		an SQL query as a String.
	 */
	private String buildQuery(String wClause) {
		
		//String query = "SELECT id, name, description, teacher_id FROM Subject, Teacher_Subject ";
		String query = "SELECT id, name, description FROM Subject";
		if (wClause.length() > 0) {
			query = query + " where " + wClause;
		}

		return query;	

	}

	/**
	 * builds an object of type Subject based on the resultset of the SQL query. Returns this
	 * object to the caller.
	 * 
	 * @param 	rs		ResultSet		the resultset from the SQL query.
	 * @return	s		Subject			the build Subject object.
	 */
	private Subject buildObject(ResultSet rs) {
		Subject s = null;
		
		try {
			s = new Subject(rs.getInt("id"),
							rs.getString("name"),
							rs.getString("description"));
			
//			Statement stmt = this.dbConnection.createStatement();
//			stmt.setQueryTimeout(5);
//			String query = "SELECT teacher_id from Teacher_Subject where subject_id = " + rs.getInt("id");
//			ResultSet rs2 = stmt.executeQuery(query);
//			while(rs2.next()) {
//				s.addTeacher(new Teacher(rs2.getInt("teacher_id")));
//			}
		}
		catch(Exception e) {
            System.out.println("DEBUG MESSAGE [SUBJECT]: buildObject(): " + e.getMessage());
		}
		
		return s;
		
	}

	

	
}